﻿DECLARE @MONTH NVARCHAR(100)=(SELECT [MAX_MONTH] FROM [DATE_EM_REPORT])
DECLARE @STARTOFMONTH DATETIME= (DATEADD(M,DATEDIFF(MONTH, 0, GETDATE()),0))
DECLARE @DATELASTYEAR nvarchar(8)= CONVERT(NVARCHAR(8),(DATEADD(M,-11,@STARTOFMONTH)),112)
DECLARE @DATE_UPDATE NVARCHAR(100)=(select [DATE_UPDATE] from [DATE_EM_REPORT])


if((select max([month]) from sp_saoke_luyke_raw) < @MONTH)
begin
 select * 
 into sp_saoke_luyke_raw 
 from [GIANGLH3].[RISK].[dbo].[SAOKE_LUYKE] 
 where custid in (select cif from TBL_CUSTOMER) and [month] > @MONTH

end
 
 update sp_saoke_luyke_raw
 set CT#UUDAI#VAY = '00-Khong tham gia CT uu dai vay' where CT#UUDAI#VAY is null

 select dao, sanpham_group, [month], count(contract_no) as quantity, sum([so du]) as balance 
 into sp_loan_product_raw
 from sp_saoke_luyke_raw
 where dao is not null
 group by dao, sanpham_group, [month]
order by month, dao

 select dao, sanpham_group, [month], count(contract_no) as quantity, sum([so du]) as balance 
 into sp_loan_product_new_raw
 from sp_saoke_luyke_raw
 where dao is not null and CONVERT(NVARCHAR(6),[VALUE DATE],112)=[MONTH]
 group by dao, sanpham_group, [month]
order by month, dao

select dao, CT#UUDAI#VAY, [month], count(contract_no) as quantity, sum([so du]) as balance 
 into sp_loan_ct_uudai_raw
 from sp_saoke_luyke_raw
 where dao is not null
 group by dao, CT#UUDAI#VAY, [month]
order by month, dao

select dao, CT#UUDAI#VAY, [month], count(contract_no) as quantity, sum([so du]) as balance 
 into sp_loan_ct_uudai_new_raw
 from sp_saoke_luyke_raw
 where dao is not null and CONVERT(NVARCHAR(6),[VALUE DATE],112)=[MONTH]
 group by dao, CT#UUDAI#VAY, [month]
order by month, dao

delete from TBL_SP_LOAN_EOP

DECLARE @MONTH1 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -1, GETDATE())),112)
DECLARE @MONTH2 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -2, GETDATE())),112)
DECLARE @MONTH3 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -3, GETDATE())),112)
DECLARE @MONTH4 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -4, GETDATE())),112)
DECLARE @MONTH5 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -5, GETDATE())),112)
DECLARE @MONTH6 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -6, GETDATE())),112)
DECLARE @MONTH7 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -7, GETDATE())),112)
DECLARE @MONTH8 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -8, GETDATE())),112)
DECLARE @MONTH9 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -9, GETDATE())),112)
DECLARE @MONTH10 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -10, GETDATE())),112)
DECLARE @MONTH11 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, -11, GETDATE())),112)
DECLARE @MONTH0 NVARCHAR(6)= CONVERT(NVARCHAR(6),(DATEADD(M, 0, GETDATE())),112)
exec('
select * into #tam3
from
(select month, dao,CT#UUDAI#VAY, col, value from sp_loan_ct_uudai_raw
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for month in (['+@MONTH1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  insert into TBL_SP_LOAN_EOP 
  select dao, CT#UUDAI#VAY, col,
  ['+@MONTH0+'] as [month0],['+@MONTH1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],1 as Group_Type, 2 as group_product from #tam3

  select * into #tam4
from
(select month, dao,CT#UUDAI#VAY, col, value from sp_loan_ct_uudai_new_raw
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for month in (['+@MONTH1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  insert into TBL_SP_LOAN_EOP 
  select dao, CT#UUDAI#VAY, col,
  ['+@MONTH0+'] as [month0],['+@MONTH1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],2 as Group_Type, 2 as group_product from #tam4
')

exec('
select * into #tam1
from
(select month, dao,sanpham_group, col, value from sp_loan_product_raw
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for month in (['+@MONTH1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  select * into #tam2
from
(select month, dao,sanpham_group, col, value from sp_loan_product_new_raw
cross apply
( select ''quantity'', cast(quantity as numeric) union all
  select ''balance'', cast(balance as numeric) ) c(col, value)) d
pivot
( max(value)
  for month in (['+@MONTH1+'],['+@MONTH2+'],['+@MONTH3+'],['+@MONTH4+'],['+@MONTH5+'],['+@MONTH6+'],['+@MONTH7+'],['+@MONTH8+'],['+@MONTH9+'],['+@MONTH10+'],['+@MONTH11+'],['+@MONTH0+'])) piv;

  insert into TBL_SP_LOAN_EOP 
  select dao, sanpham_group, col,
  ['+@MONTH0+'] as [month0],['+@MONTH1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],1 as Group_Type, 1 as group_product  from #tam1

  insert into TBL_SP_LOAN_EOP 
  select dao, sanpham_group, col,
  ['+@MONTH0+'] as [month0],['+@MONTH1+'] as [month1],['+@MONTH2+'] as [month2],['+@MONTH3+'] as [month3],['+@MONTH4+'] as [month4],['+@MONTH5+'] as [month5],['+@MONTH6+'] as [month6],['+@MONTH7+'] as [month7],['+@MONTH8+'] as [month8],['+@MONTH9+'] as [month9],['+@MONTH10+'] as [month10],['+@MONTH11+'] as [month11],2 as Group_Type, 1 as group_product  from #tam2
')


declare @count int = 0

  while (@count <12)
  begin 
  exec('
  update TBL_SP_LOAN_EOP set month'+@count+'=0 where MONTH'+@count+' is null

  --update TBL_SP_LOAN_EOP set month'+@count+'=0 where col=''balance'' and group_type=2
  ')
  set @count = @count+1
  end

select * into sp_LOAN_last_date from SERVER74.BICDATA.DBO.BALANCE_LIVE where BUSINESS_DATE= @DATE_UPDATE AND APP='LOAN'

--SELECT distinct CT_UUDAI_VAY FROM sp_LOAN_last_date WHERE CIF IN (SELECT CIF FROM TBL_CUSTOMER)

alter table sp_LOAN_last_date
add SAN_PHAM_LOAN nvarchar(100)

update sp_LOAN_last_date
set SAN_PHAM_LOAN='Unsecured'
where chuong_trinh_sp like '%bil%' or chuong_trinh_sp like '%Clean%'

update sp_LOAN_last_date
set SAN_PHAM_LOAN='CAR'
where chuong_trinh_sp like '%CAR%' and SAN_PHAM_LOAN is null

update sp_LOAN_last_date
set SAN_PHAM_LOAN='CAR'
where  [SUB_CATEGORY] IN ('101','102','103','104','105','106') and SAN_PHAM_LOAN is null

UPDATE A
SET a.SAN_PHAM_LOAN=B.[MAP LAI (DUNG DE MAP)]
FROM GIANGLH3.RISK.DBO.MAP_SANPHAM_FINAL B,sp_LOAN_last_date A
WHERE A.PRODUCT_CODE = B.[VPB LN PR]
AND a.SAN_PHAM_LOAN IS NULL
						
alter table sp_LOAN_last_date
add NHOM_SP_LOAN nvarchar(100)

UPDATE sp_LOAN_last_date
 SET NHOM_SP_LOAN = CASE WHEN SAN_PHAM_LOAN = 'CAR' THEN '1.CAR'
                    WHEN  SAN_PHAM_LOAN IN ('WC reducing','WC installment','WC Revolving',N'Cho vay bổ sung vốn lưu động ngắn hạn thông thường') THEN '2.WORKING_CAPITAL'
					WHEN  SAN_PHAM_LOAN = 'Project/Property Finance' THEN '3.PROJECT/PROPERTY FINANCE'
					WHEN  SAN_PHAM_LOAN = 'Unsecured' THEN '4.UNSECURED'
					WHEN  SAN_PHAM_LOAN = 'CREDIT CARD' THEN '5.CREDIT_CARD'
					WHEN  SAN_PHAM_LOAN in ('OTHER','Valuable paper') THEN '6.OTHER'
					ELSE '6.OTHER'
					END

UPDATE sp_LOAN_last_date
SET CT_UUDAI_VAY = '00-Khong tham gia CT uu dai vay'
WHERE CT_UUDAI_VAY IS NULL


SELECT COUNT( ACCTNO) AS QUANTITY, SUM(BAL_QD) AS BALANCE, DAO, NHOM_SP_LOAN 
INTO SP_LOAN_BY_DATE
 FROM sp_LOAN_last_date 
 WHERE CIF IN (SELECT CIF FROM TBL_CUSTOMER)
GROUP BY DAO, NHOM_SP_LOAN

SELECT COUNT( ACCTNO) AS QUANTITY, SUM(BAL_QD) AS BALANCE, DAO, CT_UUDAI_VAY 
INTO SP_LOAN_UU_DAI_BY_DATE
 FROM sp_LOAN_last_date 
 WHERE CIF IN (SELECT CIF FROM TBL_CUSTOMER)
GROUP BY DAO, CT_UUDAI_VAY

update TBL_SP_LOAN_EOP set month0= b.balance
 from TBL_SP_LOAN_EOP as a, SP_LOAN_BY_DATE as b
 where a.dao=b.dao and a.sanpham_group = b.NHOM_SP_LOAN and a.group_type= 1 AND A.GROUP_PRODUCT=1 and col='balance'

 update TBL_SP_LOAN_EOP set month0= b.QUANTITY
 from TBL_SP_LOAN_EOP as a, SP_LOAN_BY_DATE as b
 where a.dao=b.dao and a.sanpham_group = b.NHOM_SP_LOAN and a.group_type= 1 AND A.GROUP_PRODUCT=1 and col='QUANTITY'

 
update TBL_SP_LOAN_EOP set month0= b.balance
 from TBL_SP_LOAN_EOP as a, SP_LOAN_UU_DAI_BY_DATE as b
 where a.dao=b.dao and a.sanpham_group = b.CT_UUDAI_VAY and a.group_type= 1 AND A.GROUP_PRODUCT=2 and col='balance'

 update TBL_SP_LOAN_EOP set month0= b.QUANTITY
 from TBL_SP_LOAN_EOP as a, SP_LOAN_UU_DAI_BY_DATE as b
 where a.dao=b.dao and a.sanpham_group = b.CT_UUDAI_VAY and a.group_type= 1 AND A.GROUP_PRODUCT=2 and col='QUANTITY'

select * from TBL_SP_LOAN_EOP ORDER BY DAO, SANPHAM_GROUP

update TBL_SP_LOAN_EOP 
set sanpham_group = case 
when sanpham_group = '1.CAR' then '1. Car'
when sanpham_group= '2.WORKING_CAPITAL' then '2. Working Capital'
when sanpham_group = '3.PROJECT/PROPERTY FINANCE' then '3. Project/Property Finance'
when sanpham_group = '4.UNSECURED' then '4. Unsecured'
when sanpham_group = '6.OTHER' then '6. Other' end

 delete from  [SERVER12].[SMECustomer360].dbo.TBL_SP_LOAN_EOP
  insert [SERVER12].[SMECustomer360].dbo.TBL_SP_LOAN_EOP
select * from TBL_SP_LOAN_EOP


--select * from TBL_HR

